package com.houduan.dao;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.Map;

public class StatisticsDao {

    private static final String URL = "jdbc:mysql://localhost:3306/netbar";
    private static final String USERNAME = "root";
    private static final String PASSWORD = "rjxy123456";

    // 获取数据库连接
    private Connection getConnection() throws SQLException {
        return DriverManager.getConnection(URL, USERNAME, PASSWORD);
    }

    // 获取每日订单量统计
    public Map<String, Integer> getDailyOrderCount() {
        Map<String, Integer> dailyOrders = new HashMap<>();
        String sql = "SELECT DATE(create_time) as order_date, COUNT(*) as order_count FROM orders GROUP BY DATE(create_time)";

        try (Connection connection = getConnection();
             PreparedStatement preparedStatement = connection.prepareStatement(sql);
             ResultSet resultSet = preparedStatement.executeQuery()) {

            while (resultSet.next()) {
                String date = resultSet.getString("order_date");
                int count = resultSet.getInt("order_count");
                dailyOrders.put(date, count);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return dailyOrders;
    }

    // 获取每日收入统计
    public Map<String, Double> getDailyRevenue() {
        Map<String, Double> dailyRevenue = new HashMap<>();
        String sql = "SELECT DATE(create_time) as revenue_date, SUM(amount) as total_revenue FROM orders WHERE status = '已支付' GROUP BY DATE(create_time)";

        try (Connection connection = getConnection();
             PreparedStatement preparedStatement = connection.prepareStatement(sql);
             ResultSet resultSet = preparedStatement.executeQuery()) {

            while (resultSet.next()) {
                String date = resultSet.getString("revenue_date");
                double revenue = resultSet.getDouble("total_revenue");
                dailyRevenue.put(date, revenue);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return dailyRevenue;
    }

    // 获取用户统计数据
    public Map<String, Integer> getUserStatistics() {
        Map<String, Integer> userStats = new HashMap<>();

        String totalUsersSql = "SELECT COUNT(*) as total_users FROM users";
        String activeUsersSql = "SELECT COUNT(*) as active_users FROM sessions WHERE end_time IS NULL";
        String newUsersSql = "SELECT COUNT(*) as new_users FROM users WHERE create_time >= DATE_SUB(CURRENT_DATE, INTERVAL 7 DAY)";

        try (Connection connection = getConnection()) {
            // 总用户数
            try (PreparedStatement totalStmt = connection.prepareStatement(totalUsersSql);
                 ResultSet totalRs = totalStmt.executeQuery()) {
                if (totalRs.next()) {
                    userStats.put("totalUsers", totalRs.getInt("total_users"));
                }
            }

            // 活跃用户数
            try (PreparedStatement activeStmt = connection.prepareStatement(activeUsersSql);
                 ResultSet activeRs = activeStmt.executeQuery()) {
                if (activeRs.next()) {
                    userStats.put("activeUsers", activeRs.getInt("active_users"));
                }
            }

            // 新注册用户数
            try (PreparedStatement newStmt = connection.prepareStatement(newUsersSql);
                 ResultSet newRs = newStmt.executeQuery()) {
                if (newRs.next()) {
                    userStats.put("newUsers", newRs.getInt("new_users"));
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return userStats;
    }

    // 获取电脑使用统计数据
    public Map<String, Object> getComputerUsageStatistics() {
        Map<String, Object> computerUsageStats = new HashMap<>();

        String totalUsageSql = "SELECT SUM(duration) as total_usage FROM sessions WHERE duration IS NOT NULL";
        String peakUsageSql = "SELECT HOUR(start_time) as peak_hour, COUNT(*) as usage_count FROM sessions WHERE start_time IS NOT NULL GROUP BY HOUR(start_time) ORDER BY usage_count DESC LIMIT 1";

        try (Connection connection = getConnection()) {
            // 总使用时长
            try (PreparedStatement totalStmt = connection.prepareStatement(totalUsageSql);
                 ResultSet totalRs = totalStmt.executeQuery()) {
                if (totalRs.next()) {
                    computerUsageStats.put("totalUsage", totalRs.getDouble("total_usage"));
                }
            }

            // 高峰使用时段
            try (PreparedStatement peakStmt = connection.prepareStatement(peakUsageSql);
                 ResultSet peakRs = peakStmt.executeQuery()) {
                if (peakRs.next()) {
                    computerUsageStats.put("peakHour", peakRs.getInt("peak_hour"));
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return computerUsageStats;
    }
}
